﻿@model DatabasesModel
@{
    Layout = "Databases.Modal.cshtml";
    var s = Model.Instance;
    var indexes = s.GetIndexInfoDetiled(Model.Database);
    var serverStartTime = Model.Instance.ServerProperties.Data?.SQLServerStartTime;
    var unusedIndexes = indexes.Data.Where(i => !i.IsPrimaryKey)
                               .OrderBy(i => i.UserLookups + i.UserScans + i.UserSeeks)
                               .ThenByDescending(i => i.UserUpdates)
                               .ThenByDescending(i => i.TotalBytes);
}
@section top {
    <span class="pull-right small">@Poll.Now(indexes)</span>
}
@if (indexes.Data == null || !indexes.Successful)
{
    <div class="alert alert-warning">
        <h5>There was an error fetching table info from @s.Name for @Model.Database</h5>
        <p class="error-stack">@indexes.ErrorMessage</p>
    </div>
}
else
{
    <div class="alert alert-dismissible alert-info" style="margin-bottom: 2px;">
        These are statistics are since SQL Server was last restarted: <strong>@(serverStartTime?.ToRelativeTimeSpan())</strong>. Remember that "unused since restart" is not "never used". <br />
        Some indexes are vital for rare operations that couldn't complete in a reasonable time otherwise, or just haven't been used yet since a server restart. Use this informtion in context.
    </div>
    <table class="table table-striped table-hover text-nowrap table-super-condensed table-responsive js-database-indexes">
        <thead>
            <tr>
                <th></th>
                <th>Table</th>
                <th>Index</th>
                <th>Size</th>
                <th>Rows</th>
                <th>Seeks</th>
                <th>Scans</th>
                <th>Lookups</th>
                <th>Updates</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var i in unusedIndexes)
            {
                <tr data-obj="@(i.SchemaName).@(i.TableName).@(i.IndexName)" class="js-next-collapsible">
                    <td class="text-muted">
                        @if (i.IsPrimaryKey)
                        {
                            <i class="fa fa-key fa-fw text-warning" aria-hidden="true" title="Primary Key"></i>
                        }
                        else if (i.IsUnique)
                        {
                            <i class="fa fa-fw fa-snowflake-o" title="Unique Index"></i>
                        }
                        else
                        {
                            <i class="fa fa-fw fa-list-alt" title="Index"></i>
                        }
                        @if (i.IsFiltered)
                        {
                            <i class="fa fa-fw fa-filter" title="Filtered Index"></i>
                        }
                    </td>
                    <td><span class="text-muted">@(i.SchemaName).</span><span>@i.TableName</span></td>
                    <td>
                        <a href="#/db/@(Model.Database)/unusedindexes/@(i.SchemaName).@(i.TableName).@(i.IndexName)" title="@i.IndexName">
                            @i.IndexName.TruncateWithEllipsis(80)
                        </a>
                    </td>
                    <td data-val="@i.TotalBytes">@(i.TotalBytes.ToSize().ToMutedIfNA())</td>
                    <td data-val="@i.RowCount">@(i.RowCount.ToComma())</td>
                    <td data-val="@i.UserSeeks">@(i.UserSeeks.ToComma())</td>
                    <td data-val="@i.UserScans">@(i.UserScans.ToComma())</td>
                    <td data-val="@i.UserLookups">@(i.UserLookups.ToComma())</td>
                    <td data-val="@i.UserUpdates">@(i.UserUpdates.ToComma())</td>
                </tr>
                <tr class="tablesorter-childRow hidden">
                    <td colspan="9">
                        <div class="panel-body">
                            <div class="list-group list-group-sm">
                                <div class="list-group-item">
                                    <div>
                                        @if (i.IsPrimaryKey)
                                        {
                                            <i class="fa fa-key fa-fw text-warning" aria-hidden="true" title="Primary Key"></i>
                                        }
                                        else
                                        {
                                            <i class="fa fa-list-alt fa-fw" aria-hidden="true"></i>
                                        }
                                        @i.IndexName
                                        <span class="text-muted">(@i.IndexType@(i.IsUnique ? ", Unique" : null)@(i.IsFiltered ? ", Filtered" : null))</span>
                                    </div>
                                    <div>
                                        <span class="text-muted">Columns:</span> @(i.KeyDefinition?.Replace("[","").Replace("]",""))
                                    </div>
                                    @if (i.IncludeDefinition.HasValue())
                                    {
                                        <div>
                                            <span class="text-muted">Included:</span> @(i.IncludeDefinition?.Replace("[", "").Replace("]", ""))
                                        </div>
                                    }
                                    @if (i.IsFiltered && i.FilterDefinition?.Length > 2)
                                    {
                                        <div>
                                            <span class="text-muted">Filter:</span> @(i.FilterDefinition.Substring(1, i.FilterDefinition.Length-2).Replace("[", "").Replace("]", ""))
                                        </div>
                                    }
                                </div>
                                <div class="list-group-item">
                                    @if (i.LastUserRead.HasValue)
                                    {
                                        <div><span class="text-muted">Last Read:</span> @(i.LastUserRead?.ToRelativeTimeSpan())</div>
                                    }
                                    @if (i.LastUserUpdate.HasValue)
                                    {
                                        <div><span class="text-muted">Last Update:</span> @(i.LastUserUpdate?.ToRelativeTimeSpan())</div>
                                    }
                                    @if (i.ReservedInRowBytes > 0)
                                    {
                                        <div><span class="text-muted">In-row Bytes:</span> @i.ReservedInRowBytes.ToSize()</div>
                                    }
                                    @if (i.ReservedLobBytes > 0)
                                    {
                                        <div><span class="text-muted">LOB Bytes:</span> @i.ReservedLobBytes.ToSize()</div>
                                    }
                                    @if (i.PartitionCount > 1)
                                    {
                                        <div><span class="text-muted">Partitions:</span> @i.PartitionCount.ToComma()</div>
                                    }
                                    @if (i.PartitionFunction.HasValue())
                                    {
                                        <div><span class="text-muted">Partition Function:</span> @i.PartitionFunction</div>
                                    }
                                    @if (i.PartitionScheme.HasValue())
                                    {
                                        <div><span class="text-muted">Partition Scheme:</span> @i.PartitionScheme</div>
                                    }
                                    <div><span class="text-muted">Fill Factor:</span> @(i.FillFactor == 0 ? 100 : i.FillFactor)%</div>
                                    @if (i.StatsLastUpdated.HasValue)
                                    {
                                        <div>
                                            <span class="text-muted">Last Stats Rebuild:</span> @i.StatsLastUpdated.Value.ToRelativeTimeSpan()
                                        </div>
                                    }
                                </div>
                                <div class="list-group-item">
                                    <pre><code class="sql">@i.Definition.Trim()</code></pre>
                                </div>
                            </div>
                        </div>
                    </td>
                </tr>
                <tr class="tablesorter-childRow hidden"></tr>
            }
        </tbody>
        <tfoot>
            @if (!indexes.Data.Any())
            {
                <tr>
                    <td colspan="8">
                        <div class="no-content">There aren't any indexes in this database.</div>
                    </td>
                </tr>
            }
            else
            {
                <tr class="total-row">
                    <td colspan="8">
                        <b>Total</b> <span class="text-muted">(@indexes.Data.Count.Pluralize("index"))</span>
                    </td>
                </tr>
            }
        </tfoot>
    </table>
    <script>
        $(function () {
            $('.js-database-indexes').tablesorter({
                headers: {
                    3: { sorter: 'dataVal', sortInitialOrder: 'desc' },
                    4: { sorter: 'dataVal', sortInitialOrder: 'desc' },
                    5: { sorter: 'dataVal', sortInitialOrder: 'desc' },
                    6: { sorter: 'dataVal', sortInitialOrder: 'desc' },
                    7: { sorter: 'dataVal', sortInitialOrder: 'desc' },
                    8: { sorter: 'dataVal', sortInitialOrder: 'desc' }
                }
            });
            Status.highlight();
        });
    </script>
}
